package cn.edu.sdjzu.xg2121.dao;

import cn.edu.sdjzu.xg2121.domain.University;
import cn.edu.sdjzu.xg2121.util.CommonDao;
import cn.edu.sdjzu.xg2121.util.Condition;
import cn.edu.sdjzu.xg2121.util.JdbcHelper;
import cn.edu.sdjzu.xg2121.util.Pagination;


import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class UniversityDao {
    private  static UniversityDao universityDao = new UniversityDao();
    public static UniversityDao getInstance(){
        return universityDao;
    }
    private UniversityDao(){}
    public String update(University university)throws SQLException {
        try{
            Connection connection = JdbcHelper.getConn();
            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE university SET description=?,no=?,remarks=? where id=" + university.getId());
            preparedStatement.setString(1,university.getDescription());
            preparedStatement.setString(2,university.getNo());
            preparedStatement.setString(3,university.getRemarks());
            preparedStatement.executeUpdate();
            JdbcHelper.close(preparedStatement, connection);
            return "添加成功";
        } catch (Exception e){
            return "添加失败";
        }
    }
    public boolean delete(Integer id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM university " +
                " WHERE id=?");
        preparedStatement.setInt(1, id);
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement, connection);
        return affectedRowNum > 0;
    }
    public Collection<University> findAll(List<Condition> conditionList, Pagination pagination) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        Collection<University> desiredUniversitys = new HashSet<University>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM university");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection, "university", conditionList);
        String pginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(pginationClause);
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            String description = resultSet_select_all.getString("description");
            String no = resultSet_select_all.getString("no");
            String remarks = resultSet_select_all.getString("remarks");
            University university = new University(id, description, no, remarks);
            desiredUniversitys.add(university);
        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all, connection);
        return desiredUniversitys;
    }
    public University find(Integer id) throws SQLException {
        University desiredUniversity = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM university WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet = pstmt_select_id.executeQuery();
        if (resultSet.next()){
            int id2 = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            desiredUniversity = new University(id2,description,no,remarks);
        }
        JdbcHelper.close(resultSet,pstmt_select_id,connection);
        return desiredUniversity;
    }
}
